﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using DataAccessLayer;

namespace DataAccessLayer
{
    public class csDAL
    {
        protected string _strconn = ConfigurationManager.ConnectionStrings["con"].ConnectionString.ToString();
        private SqlConnection conn;
        private string errorstr = string.Empty;

        public csDAL()
        {
            conn = new SqlConnection();
        }

        public string ConnectionString
        {
            get { return _strconn; }
            set { _strconn = value; }
        }

        private bool Open_Connection()
        {
            if (conn == null)
                conn = new SqlConnection();
            if (conn.State == System.Data.ConnectionState.Closed)
            {
                try
                {
                    conn.ConnectionString = ConnectionString;
                    conn.Open();
                    return true;
                }
                catch (Exception a)
                {
                    errorstr += " " + a.Message;
                    return false;
                }
            }
            else
                return true;
        }

        private SqlParameter add_parameters(csparameterListType objpar)
        {
            SqlParameter sqlpar = new SqlParameter();

            sqlpar.ParameterName = objpar.name;
            sqlpar.SqlDbType = objpar.sqltype;
            sqlpar.SqlValue = objpar.value;

            return sqlpar;
        }


        public System.Data.DataSet executespreturnds(string spname, List<csparameterListType> objlist)
        {

            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter sda = new SqlDataAdapter();
            DataSet ds = new DataSet();

            if (Open_Connection())
            {
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = spname;

                foreach (csparameterListType par in objlist)
                {
                    cmd.Parameters.Add(add_parameters(par));
                }

                sda.SelectCommand = cmd;

                sda.Fill(ds);
            }

            close_conn();
            return ds;

        }

        public System.Data.DataSet executespreturnds(string spname)
        {

            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter sda = new SqlDataAdapter();
            DataSet ds = new DataSet();

            if (Open_Connection())
            {
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = spname;

                sda.SelectCommand = cmd;

                sda.Fill(ds);

            }

            close_conn();
            return ds;

        }

        public IDataReader executespreturndr(string spname, List<csparameterListType> objlist)
        {
            SqlCommand cmd = new SqlCommand();
            IDataReader dr = null;
            try
            {
                if (Open_Connection())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = spname;

                    foreach (csparameterListType par in objlist)
                    {
                        cmd.Parameters.Add(add_parameters(par));
                    }

                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                return dr;
            }
            catch (Exception e)
            {
                errorstr += " " + e.Message;
                return null;
            }
        }

        public IDataReader executespreturndr(string spname)
        {
            SqlCommand cmd = new SqlCommand();
            IDataReader dr = null;

            try
            {
                if (Open_Connection())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = spname;
                    dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                return dr;
            }
            catch (Exception e)
            {
                errorstr += " " + e.Message;
                return null;
            }
        }

        public void executespreturnnd(string spname)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                if (Open_Connection())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = spname;
                    cmd.ExecuteNonQuery();
                }

                close_conn();
            }
            catch (Exception e)
            {
                errorstr += " " + e.Message;
            }
        }

        public void executespreturnnd(string spname, List<csparameterListType> objlist)
        {
            SqlCommand cmd = new SqlCommand();
            try
            {
                if (Open_Connection())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.CommandText = spname;

                    foreach (csparameterListType par in objlist)
                    {
                        cmd.Parameters.Add(add_parameters(par));
                    }

                    cmd.ExecuteNonQuery();
                }
                close_conn();
            }
            catch (Exception e)
            {
                errorstr += " " + e.Message;
            }
        }

        private void close_conn()
        {
            if (conn.State == System.Data.ConnectionState.Open)
                conn.Close();

            conn = null;
        }

        public IDataReader executespreturndr(string p, int _idno)
        {
            throw new NotImplementedException();
        }
    }
}

